﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Data.SqlClient;
using System.Text;
using System.Data;

namespace ISODOC.Models
{
    public class MasterModel
    {
        public class Mast_DS
        {
            public string _Code { get; set; }
            public string _Type { get; set; }            
            public string _ModifyBy { get; set; }
            public string _ModifyDT { get; set; }
        }
        #region "DOC TYPE"
        public List<Mast_DS> SEL_DOC_MASTER(string code)
        {

            string ConnectionString =WinNemoLib.Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            string wh = " where ";
            str.AppendLine(" select * from " + WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".dbo.DOCTYPE  ");
            if (StrUtil.IsNotEmpty(code))
            {
                str.Append(wh + "  DOCTYPE = '" + code + "'");
                wh = " and ";
            }


            SqlCommand cmd = new SqlCommand(str.ToString() + " order by DOCTYPE", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;


            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<Mast_DS> list = new List<Mast_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }
            Mast_DS model = new Mast_DS();
            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                model = new Mast_DS();
                model._Code = items["DOCTYPE"].ToString().ToUpper();
                model._Type = items["NAME"].ToString().ToUpper();
                list.Add(model);
            }
           
            return list;
        }

        public Boolean Ins_DocType(ConnectionWrapper cnn, Mast_DS incoming)
        {
            try
            {
                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" INSERT INTO ");
                comstr.AppendLine(WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".[dbo].[DOCTYPE]");
                comstr.AppendLine(" (DOCTYPE,[Name]) VALUES (");

                comstr.AppendLine(" @doctype,@name) ");

                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();
                cmd.AddParameterStringWithValue("@doctype", incoming._Code.ToString());
                cmd.AddParameterStringWithValue("@name", incoming._Type.ToUpper());
                if (cmd.ExecuteNonQuery() <= 0)
                {
                    cnn.RollbackTransaction();
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }

        }

        public Boolean Edit_DocType(ConnectionWrapper cnn, Mast_DS incoming)
        {
            try
            {
                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" UPDATE ");
                comstr.AppendLine(WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".[dbo].[DOCTYPE]");
                comstr.AppendLine(" set");
                comstr.AppendLine("[Name]=@name");
                comstr.AppendLine(" where DOCTYPE = @doctype");
                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();
                cmd.AddParameterStringWithValue("@doctype", incoming._Code.ToString());
                cmd.AddParameterStringWithValue("@name", incoming._Type.ToUpper());

                if (cmd.ExecuteNonQuery() <= 0)
                {
                    cnn.RollbackTransaction();
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }

        }
        #endregion


        #region "OPT TYPE"
        public List<Mast_DS> SEL_OPT_MASTER(string code)
        {

            string ConnectionString = WinNemoLib.Nemo.GetConfiguredDbConnectionWrappertostring("Database");

            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            string wh = " where ";
            str.AppendLine(" select * from " + WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".dbo.OPTTYPE  ");
            if (StrUtil.IsNotEmpty(code))
            {
                str.Append(wh + "  OPTTYPE = '" + code + "'");
                wh = " and ";
            }


            SqlCommand cmd = new SqlCommand(str.ToString() + " order by OPTTYPE", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;


            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<Mast_DS> list = new List<Mast_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }
            Mast_DS model = new Mast_DS();
            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                model = new Mast_DS();
                model._Code = items["OPTTYPE"].ToString().ToUpper();
                model._Type = items["NAME"].ToString().ToUpper();
                list.Add(model);
            }

            return list;
        }

        public Boolean Ins_OptType(ConnectionWrapper cnn, Mast_DS incoming)
        {
            try
            {
                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" INSERT INTO ");
                comstr.AppendLine(WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".[dbo].[OPTTYPE]");
                comstr.AppendLine(" (OPTTYPE,[Name]) VALUES (");

                comstr.AppendLine(" @opttype,@name) ");

                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();
                cmd.AddParameterStringWithValue("@opttype", incoming._Code.ToString());
                cmd.AddParameterStringWithValue("@name", incoming._Type.ToUpper());
                if (cmd.ExecuteNonQuery() <= 0)
                {
                    cnn.RollbackTransaction();
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }

        }

        public Boolean Edit_OptType(ConnectionWrapper cnn, Mast_DS incoming)
        {
            try
            {
                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" UPDATE ");
                comstr.AppendLine(WinNemoLib.Nemo.GetINI("Dbase", "Base") + ".[dbo].[OPTTYPE]");
                comstr.AppendLine(" set");
                comstr.AppendLine("[Name]=@name");
                comstr.AppendLine(" where DOCTYPE = @opttype");
                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();
                cmd.AddParameterStringWithValue("@opttype", incoming._Code.ToString());
                cmd.AddParameterStringWithValue("@name", incoming._Type.ToUpper());

                if (cmd.ExecuteNonQuery() <= 0)
                {
                    cnn.RollbackTransaction();
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }

        }
        #endregion
    }
}